import os
import glob
import shutil
import pandas as pd

problem_files_folder = r'D:\dl\problems'
os.makedirs(problem_files_folder, exist_ok=True)


def excel_to_txt(excel_file, txt_file):
    try:
        with open(excel_file, "r") as file:
            file.close()
    # Raise error if the file is opened before
    except IOError:
        print(f"{excel_file} 正被占用无法操作，跳过")
        return
    # sheet_name=0表示第一个表
    # https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html
    try:
        df = pd.read_excel(excel_file, sheet_name=0, header=None)
        with open(txt_file, 'w', encoding='utf-8') as wf:
            # index=True则输出从0开始的行号
            # https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_string.html
            df.to_string(wf, header=False, index=False)
            print(excel_file, '-->', txt_file)
    except Exception as e:
        # 遇到问题文件，则输出文件名和错误原因，复制文件到指定文件夹以便调查，继续处理其他文件
        print(excel_file, e)
        shutil.copy2(excel_file, problem_files_folder)


def extract_bookmarks(excel_folder, txt_folder):
    for excel_file in glob.glob(rf'{excel_folder}\**\*.xlsx', recursive=True):
        txt_file = excel_file.replace(excel_folder, txt_folder).replace('.xlsx', '.txt')
        os.makedirs(os.path.dirname(txt_file), exist_ok=True)
        excel_to_txt(excel_file, txt_file)


if __name__ == '__main__':
    excel_folder = r'D:\dl\样本数据\输入数据'
    txt_folder = r'D:\dl\txt'
    extract_bookmarks(excel_folder, txt_folder)
